SSIS – SQL Server Integration Services: Description of services

SSIS: Microsoft SQL Server includes the SQL Server Integration Services component, and today we will learn what this component is for and take a look at its main features.

As you probably know, Microsoft SQL Server includes not only the main component of Database Engine, but also many other useful components, for example, earlier we saw SQL Server Reporting Services – it’s a complete component for creating and publishing reports. Now we have Integration Services next in line, which are also very useful and important. So, let’s get started.

SQL Server Integration Services

SQL Server Integration Services (SSIS) is a Microsoft SQL Server component that is designed to automate the extraction, transformation and consolidation of data from all kinds of sources to other data sources. Integration Services can also be used to automate other processes, such as database maintenance tasks or some actions with files in the operating system, in general, SSIS can act as a kind of platform for the automation of almost everything.

This is actually the case, for example, when I first learned about what you can do with SSIS I was surprised. Therefore, I suggest that you first look at the main features of SQL Server Integration Services so that you have a rough idea of what SSIS is capable of, of course we will not look at everything, but the general points will be covered, and then move on to an overview of the functionality and tools that are used in SQL Server Integration Services.

Note! All of the features below, as well as functionality and tools to work with SSIS are reviewed in the new versions of Microsoft SQL Server 2008 R2, of course, there are more features and functionality and tools are better.

Key features of SQL Server Integration Services

• With SSIS, you can easily import and export data from one source to another. For this purpose, Microsoft has even developed a special separate tool, i.e. a kind of “Wizard”, which performs these actions as easily as possible. We talked about it in the material “Tool for data import and export in MS Sql 2008”. And if anyone remembers, in one of the last windows there is an offer to save all the actions in the SSIS package, so that you can edit and execute it later. In other words, everything that the “Import and Export Wizard” is capable of can be automated through the SSIS package;
• Automating a simple import and export is certainly good, but Integration Services can do much more. Let’s assume, it is necessary for you to carry out information gathering (to unload data) from several sources, different, i.e. for example, from MS SQL Server, from some Access database, from pair of text files and others. Then it is necessary to transform, unite, filter and divide these data on appointment, for example, to each user it is necessary to give only that part of the information for which it is responsible. And then all it to unload, and in that format which is convenient for the concrete user, for example, to one it is convenient to work in Excel, and another, the advanced user, wants to see these data in Access. And as a result to decompose these files on folders which have specified users. All this can be done within one SSIS package. And this package can be expanded, it all depends on your needs. Here is another situation where SSIS will be very useful and will save you a lot of time, let’s say you need to organize the filling of some data warehouse on a permanent basis from different data sources. SQL Server Integration Services supports a lot of different data sources with which it can communicate, such as, of course, the above SQL Server, Access databases, Excel files, XML files, text files and others (read more about this in the electronic documentation on SQL Server);
• In SQL Server Integration Services package you can include tasks to run executable files (exe) or batch files (bat), which, as you understand, allows you to automate tasks not related to data import and export at all. For example, you need to run one program that will do some calculations, and when it finishes running another program, and when it finishes, the third well, in general, the ability to run programs from the package, and how to use it, of course, you decide;
• SQL Server Integration Services allows you to perform tasks related to database maintenance, i.e. administrative functions. For these purposes, SSIS even created a special block “Tasks Service Plan”, which includes such tasks as: backup database, compress the database, cleaning the log, update statistics, index rebuilding and others. In other words, if you have similar tasks and they are not automated, you can automate them using SSIS packages;
• With the help of Integration Services you can access objects and SQL server data and perform operations on them, such as inserting, deleting or modifying data. In other words, you can execute SQL instructions or stored procedures from SSIS packages;
• SSIS can also work with the file system, i.e. perform operations related to files and directories, for example, create, copy, move or delete files and folders. Suppose you need to automate the process of copying some files on a permanent basis, this can be done with the help of both simple bat files and SSIS packages.
• In SQL Server Integration Services it is possible to send e-mail messages. For example, you want to receive an email about the successful or unsuccessful completion of a task.

As you can see, this is not a complete list of features of Integration Services, but the main feature is that all of the above can be combined in one package. In other words, you can create an SSIS package in which, say, you first back up the database as well as other sources (such as Excel files).

Then you run the stored procedure, which updates the data in the SQL server database, then you unload data from all these sources, merge them and deliver the data in a convenient format for the user, and so that the user knows that his data is prepared to send him an e-mail. In my opinion, SSIS capabilities are more than enough.

SQL Server Integration Services Feature

Parameters

SQL Server Integration Services has functionality that allows you to run packages with parameters, that is we can assign values to the properties within a package. In other words, we can influence the execution of a package without changing the package itself. For example, to upload from the database we need to know the path to the folder where we want to put the upload itself, so that path can be passed as a parameter.

Cycles

Integration Services allow you to organize a recurring flow of actions in a package, i.e. a cycle. SSIS has two types of loops that are containerized, a “Cycle by Element”, it works similarly to the structure of the For loop and a “Cycle by Element”, i.e. a kind of Foreach loop. We can use loops, for example, when we need to perform one task a certain number of times and only then proceed to another task. Or, for example, using a loop for each item, we can do something with each file in the folder.

Variables

Variables can be used in SSIS, i.e. during the execution of the package we can store some temporary values in variables and use them, for example in tasks or containers. Variables can be useful for us, for example, to pass to SQL instruction of a parameter that will be formed dynamically or, for example, in a loop to control iteration or in the “Derived column” task during data flow conversion.

There are two types of variables in Integration Services: custom and system variables. Custom variables are those that you and I can create, and system variables are those that are defined by Integration Services. For example, system variables include StartTime, which stores the time when a package was launched, or UserName, which shows the name of the user account that launched the package.

Expanding functionality using scripts in Visual Basic or C#

SQL Server Integration Services allows you to extend standard features by writing scripts in Visual Basic or C# programming languages. In other words, if you don’t have enough built-in SSIS tasks, you can write your own task. For this purpose, there is a built-in task “Script” that allows you to write scripts in Visual Basic or C# in Visual Studio environment, and all the capabilities provided by Microsoft .NET Framework platform will be also available.

Transactions in the package

To ensure data integrity, SSIS has a transaction mechanism that allows you to commit or rollback all changes made during the execution of tasks included in the transaction. For example, a package performs several tasks related to data update, we can combine these tasks into a transaction, and in this case they will be considered a single whole, ie the result of these two tasks will be one, or successful, and all changes are saved, or unsuccessful, and all changes are canceled.

Save Points

In Integration Services, the package can be configured to use saving points (control points). The mechanism is as follows, if the package is configured to use checkpoints, then the information about the execution of the package is written to a special file, and it is used when restarting the failed package in order to start the package from the moment it crashes. If the package has been successfully executed, the checkpoint file is deleted and then recreated at a new startup.

For example, you have several tasks that download large amounts of data, and let’s say one task was completed successfully, and it was running for, say, a couple of hours, and the second task had a connection failure. If you use save points, then if the failure is corrected, you don’t have to wait two hours for the first task to complete because the package knows it’s already completed successfully.

Tracking and intercepting errors

During the execution of the SSIS package, unexpected errors may occur, and the Integration Services provide error tracking and capture functionality. In other words, if a bug occurs in a package, we can track the bug and hand it over to another task or event handler so that the package does not crash but works correctly.

Logging

In order to keep track of everything that happens inside the package, SQL Server Integration Services has a feature that allows you to keep a log that will record all events during the execution of the package. SSIS has several options for logging, such as writing everything to a text or XML file, or, for example, a Windows event log, and of course, there is the option to save the log entries to the SQL Server database.

Tools for working with SQL Server Integration Services

Let’s look at a few tools and programs that are part of the Integration Services.

SQL Server Integration Services

This is the Windows service that handles packet processing. You can only install it on a computer in one instance.

Environment SQL Server Business Intelligence Development Studio

BIDS is a graphical environment for package development. The SSIS package development process is a kind of visual programming, i.e. the whole package logic will be displayed graphically. The Business Intelligence Development Studio environment is just for programming SSIS packages.

SQL Server Management Studio Environment

I think that you are already familiar with this environment; in Integration Services it acts as a packet management tool as well as a monitoring tool, i.e. you can see which packages are currently running. You can also use the Management Studio to run, import, export packages and create directories in the file system to store them.

Command line programs

You can launch packages and manage them from the command line using the following programs:

dtexec – a program to launch packages;
dtutil – a program for managing packages.

SSIS Tutorial For Beginners. SQL Server Integration Services (SSIS)